package com.turing.post.transaction.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.report.util.MerchantDaySettlement;
import com.turing.post.transaction.util.MchTradDetail;
import com.turing.post.transaction.util.MchTradDetailDao;
import com.turing.post.transaction.util.MrchSettResult;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

@Stateless(mappedName = "transaction.impl.MchTradDetailDaoImpl")
@Remote
@Local
public class MchTradDetailDaoImpl implements MchTradDetailDao {
	/**
	 * 实体管理器
	 * 
	 * @see javax.persistence.EntityManager
	 */
	@PersistenceContext
	protected EntityManager _em;

	public static final String allcolumns = "MerchantNo,TranDate,TranSeqNo,CancelTranSeqNo,TranCode,"
			+ "TranTime,MrchSeqNo,BusinessType,FeeRatio,CardNo,Amount,"
			+ "Fee,ScorePaidAmount,FactScorePaidAmount,accAckSeqNo,MrchAbstract,"
			+ "MembershipId,VipCardNo,ScoreTranSeqNo,ScorePaidTranSeqNo,ScoreAmount,"
			+ "Score,ReScoreTime,ReScoreOperNo,ReScoreRemark,OtherTranDate,"
			+ "OtherTranSeqNo,OtherMrchSeqNo,TerminalNo,TermSettBatchNo,TermSeqNo,"
			+ "MrchTellerNo,MrchBrchNo,"
			+ "AdjustTranDate, AdjustTranSeqNo, AdjustOperId, AdjustRemark";
	private final static String grpSumSql = ""
	// 消费
			+ "nvl(sum(case when t.tranCode = '9010' then t.Amount else 0 end),0), "
			// 消费笔数
			+ "nvl(sum(case when t.tranCode = '9010' then 1 else 0 end),0), "
			// 充值
			+ "nvl(sum(case when t.tranCode = '9040' or t.tranCode = '9050' then t.Amount else 0 end),0), "
			// 充值笔数
			+ "nvl(sum(case when t.tranCode = '9040' or t.tranCode = '9050' then 1 else 0 end),0), "
			// 退货
			+ "nvl(sum(case when t.tranCode = '9020' or t.tranCode = '9030' then abs(t.Amount) else 0 end),0), "
			// 退货笔数
			+ "nvl(sum(case when t.tranCode = '9020' or t.tranCode = '9030' then 1 else 0 end),0), "
			// 合计持卡人手续费或折扣
			+ "nvl(sum(t.fee),0)";

	private final static String grpSettSumSql = ""
	// 借方金额 -- 消费、充值撤销
			+ "sum(case when t.tranCode = '9010' or t.tranCode = '9050' then abs(t.Amount) else 0 end), "
			// 借方笔数 -- 消费、充值撤销
			+ "sum(case when t.tranCode = '9010' or t.tranCode = '9050' then 1 else 0 end), "
			// 贷方金额 -- 消费撤销、退货、充值
			+ "sum(case when t.tranCode = '9020' or t.tranCode = '9030' or t.tranCode = '9040' then abs(t.Amount) else 0 end), "
			// 贷方笔数 -- 消费撤销、退货、充值
			+ "sum(case when t.tranCode = '9020' or t.tranCode = '9030' or t.tranCode = '9040' then 1 else 0 end) ";

	/**
	 * 根据商户号、终端号以及批次号统计交易数据
	 * 
	 * @param merchantNo
	 * @param terminalNo
	 * @param batchNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public MrchSettResult getMrchSettResultByTerminalBatchNo(String merchantNo,
			String terminalNo, String batchNo) {
		MrchSettResult msr = new MrchSettResult();

		String sql = "select "
				+ grpSettSumSql
				+ "from MchTradDetail t "
				+ "where t.merchantNo = ?1 and t.terminalNo = ?2 and t.termSettBatchNo = ?3 "
				+ "and COALESCE(LENGTH(TRIM(t.cancelTranSeqNo)),0) = 0";

		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantNo);
		query.setParameter(2, terminalNo);
		query.setParameter(3, batchNo);

		List<Object[]> obj_list = query.getResultList();
		if (obj_list == null || obj_list.size() != 1)
			return msr;

		// 类型转换
		for (Object[] objarr : obj_list) {
			msr.DebitAmount = new BigInteger(objarr[0].toString());
			msr.DebitCount = new BigInteger(objarr[1].toString());
			msr.CreditAmount = new BigInteger(objarr[2].toString());
			msr.CreditCount = new BigInteger(objarr[3].toString());
			break;
		}

		return msr;
	}

	/**
	 * 根据商户号、日期统计交易数据
	 * 
	 * @param merchantNo
	 * @param tranDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public MrchSettResult getMrchSettResultByMerchantNo(String merchantNo,
			Date tranDate) {
		MrchSettResult msr = new MrchSettResult();

		String sql = "select " + grpSettSumSql + "from MchTradDetail t "
				+ "where t.merchantNo = ?1 and t.tranDate = ?2 "
				+ "and COALESCE(LENGTH(TRIM(t.cancelTranSeqNo)),0) = 0";

		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantNo);
		query.setParameter(2, tranDate);

		List<Object[]> obj_list = query.getResultList();
		if (obj_list == null || obj_list.size() != 1)
			return msr;

		// 类型转换
		for (Object[] objarr : obj_list) {
			msr.DebitAmount = new BigInteger(objarr[0].toString());
			msr.DebitCount = new BigInteger(objarr[1].toString());
			msr.CreditAmount = new BigInteger(objarr[2].toString());
			msr.CreditCount = new BigInteger(objarr[3].toString());
			break;
		}

		return msr;
	}

	@SuppressWarnings("unchecked")
	@Override
	public MchTradDetail getMchTradDetailByPrimaryKey(MchTradDetail mtd) {
		if (PCPSUtil.isEmpty(mtd.getMerchantNo()) || mtd.getTranDate() == null
				|| PCPSUtil.isEmpty(mtd.getTranSeqNo()))
			return null;
		MchTradDetail _mtd = _em.find(MchTradDetail.class, mtd);
		if (_mtd != null) {
			return _mtd;
		} else {
			String sql = "select * from MchHisTradDetail where MerchantNo=?1 and TranDate=to_date(?2,'yyyy-mm-dd') and TranSeqNo=?3";
			Query query = _em.createNativeQuery(sql, MchTradDetail.class);
			query.setParameter(1, mtd.getMerchantNo());
			query.setParameter(2, PCPSUtil.getPackedYMDString2(mtd
					.getTranDate()));
			query.setParameter(3, mtd.getTranSeqNo());
			List<MchTradDetail> macc = query.getResultList();
			if (macc != null)
				return macc.get(0);
			return null;
		}
	}

	/**
	 * 插入
	 */
	public void insert(MchTradDetail mtd) throws PCPSApplicationException {
		GeneralDao.insert(_em, mtd);
	}

	/**
	 * 更新
	 */
	public void update(MchTradDetail mtd) throws PCPSApplicationException {
		GeneralDao.update(_em, mtd);
	}

	/**
	 * 重置某交易的other字段
	 * 
	 * @param merchantNo
	 *            商户号
	 * @param tranDate
	 *            交易日期
	 * @param tranSeqNo
	 *            交易流水号
	 * @return
	 */
	public int resetMchTradDetailOtherInf(String merchantNo, Date tranDate,
			String tranSeqNo) throws PCPSApplicationException {
		String sql = "update MchTradDetail t set t.otherTranDate = null, t.otherTranSeqNo = null, t.otherMrchSeqNo = null "
				+ "where t.merchantNo = ?1 and t.tranDate = ?2 and tranSeqNo = ?3";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantNo);
		query.setParameter(2, tranDate);
		query.setParameter(3, tranSeqNo);
		return query.executeUpdate();
	}

	/**
	 * 根据三要素检索商户流水
	 * 
	 * @param merchantNo
	 * @param tranDate
	 * @param mrchReqSeqNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<MchTradDetail> getMchTradDetailByMrchSeqNo(String merchantNo,
			Date tranDate, String mrchReqSeqNo) {
		String sql = "select * from MchTradDetail where merchantNo = ?1 and tranDate = ?2 and MrchSeqNo = ?3 for UPDATE";
		Query query = _em.createNativeQuery(sql, MchTradDetail.class);
		query.setParameter(1, merchantNo);
		query.setParameter(2, tranDate);
		query.setParameter(3, mrchReqSeqNo);
		List<MchTradDetail> mtdlist = query.getResultList();
		if (mtdlist == null || mtdlist.size() != 1)
			return mtdlist;
		MchTradDetail mtd = mtdlist.get(0);
		_em.refresh(mtd);
		return mtdlist;
	}

	/**
	 * 根据三要素检索商户流水历史
	 * 
	 * @param merchantNo
	 *            商户号
	 * @param tranDate
	 *            交易日期
	 * @param mrchReqSeqNo
	 *            交易流水号
	 * @return 商户交易流水对象列表
	 */
	@SuppressWarnings("unchecked")
	public List<MchTradDetail> getMchTradDetailByMrchSeqNoFromHistory(
			String merchantNo, Date tranDate, String mrchReqSeqNo) {
		String sql = "select * from MchHisTradDetail where merchantNo = ?1 and tranDate = ?2 and MrchSeqNo = ?3";
		Query query = _em.createNativeQuery(sql, MchTradDetail.class);
		query.setParameter(1, merchantNo);
		query.setParameter(2, tranDate);
		query.setParameter(3, mrchReqSeqNo);
		List<MchTradDetail> mtdlist = query.getResultList();
		return mtdlist;
	}

	/**
	 * 根据三要素检索商户流水历史
	 * 
	 * @param merchantNo
	 *            商户号
	 * @param tranDate
	 *            交易日期
	 * @param mrchReqSeqNo
	 *            交易流水号
	 * @return 商户交易流水对象列表
	 */
	@SuppressWarnings("unchecked")
	@Override
	public MchTradDetail getMchTradDetailByMrchSeqNoFromMchTradDetail(
			String merchantNo, Date tranDate, String mrchReqSeqNo) {
		String sql = "select * from MchTradDetail where merchantNo = ?1 and tranDate = ?2 and MrchSeqNo = ?3";
		Query query = _em.createNativeQuery(sql, MchTradDetail.class);
		query.setParameter(1, merchantNo);
		query.setParameter(2, tranDate);
		query.setParameter(3, mrchReqSeqNo);
		List<MchTradDetail> mtdlist = query.getResultList();
		if (mtdlist != null && mtdlist.size() > 0) {
			return mtdlist.get(0);
		}
		return null;
	}

	/**
	 * 根据三要素检索商户流水历史
	 * 
	 * @param merchantNo
	 *            商户号
	 * @param tranDate
	 *            交易日期
	 * @param tranSeqNo
	 *            交易流水号
	 * @return 商户交易流水对象列表
	 */
	@SuppressWarnings("unchecked")
	public List<MchTradDetail> getMchTradDetailByTranSeqNoFromHistory(
			String merchantNo, Date tranDate, String tranSeqNo) {
		String sql = "select * from MchHisTradDetail where merchantNo = ?1 and tranDate = ?2 and tranSeqNo = ?3";
		Query query = _em.createNativeQuery(sql, MchTradDetail.class);
		query.setParameter(1, merchantNo);
		query.setParameter(2, tranDate);
		query.setParameter(3, tranSeqNo);
		List<MchTradDetail> mtdlist = query.getResultList();
		return mtdlist;
	}

	/**
	 * 迁移交易流水到历史表中
	 * 
	 * @param merchantNo
	 *            商户号
	 * @param tranDate
	 *            帐务日期
	 */
	@TransactionAttribute(javax.ejb.TransactionAttributeType.REQUIRES_NEW)
	public void migrateMchTradDetailToHistory(String merchantNo, Date tranDate) {

		final String from = "FROM MCHTRADDETAIL WHERE MERCHANTNO = ?1 AND TRANDATE <= ?2";

		final String migstr = "INSERT INTO MCHHISTRADDETAIL (" + allcolumns
				+ ") " + "SELECT " + allcolumns + " " + from;

		final String delstr = "DELETE " + from;

		// 将流水数据迁移到历史
		Query qry = _em.createNativeQuery(migstr);

		qry.setParameter(1, merchantNo);
		qry.setParameter(2, tranDate);

		qry.executeUpdate();

		// 清除已清除数据
		qry = _em.createNativeQuery(delstr);

		qry.setParameter(1, merchantNo);
		qry.setParameter(2, tranDate);

		qry.executeUpdate();
	}

	/**
	 * 迁移指定历史交易流水到当日流水表中
	 * 
	 * @param merchantNo
	 *            商户号
	 * @param tranDate
	 *            帐务日期
	 * @param tranSeqNo
	 *            流水号
	 */
	public void migrateMchTradDetailFromHistory(String merchantNo,
			Date tranDate, String tranSeqNo) {

		final String from = "FROM MCHHISTRADDETAIL WHERE MERCHANTNO = ?1 AND TRANDATE = ?2 AND TRANSEQNO = ?3";

		final String migstr = "INSERT INTO MCHTRADDETAIL (" + allcolumns + ") "
				+ "SELECT " + allcolumns + " " + from;

		final String delstr = "DELETE " + from;

		// 将历史流水数据迁移到当日
		Query qry = _em.createNativeQuery(migstr);

		qry.setParameter(1, merchantNo);
		qry.setParameter(2, tranDate);
		qry.setParameter(3, tranSeqNo);

		qry.executeUpdate();

		// 清除已清除数据
		qry = _em.createNativeQuery(delstr);

		qry.setParameter(1, merchantNo);
		qry.setParameter(2, tranDate);
		qry.setParameter(3, tranSeqNo);

		qry.executeUpdate();
	}

	private BigInteger getValue(Object obj) {
		if (obj == null)
			return BigInteger.ZERO;
		return new BigInteger(obj.toString());
	}

	/**
	 * 根据发卡商号、某日、商户号按商户号汇总交易日结数据
	 * 
	 * @param issueCompanyNo
	 *            发卡商号
	 * @param merchantNo
	 *            商户号(可选)
	 * @param tranDate
	 *            帐务日期
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<MerchantDaySettlement> getAllMerchantDaySettlementByMerchantNo(
			String issueCompanyNo, String merchantNo, Date tranDate) {
		String sql = "select t.merchantNo, "
				+ grpSumSql
				+ "from MchTradDetail t, Merchant m "
				+ "where m.issueCompanyNo = ?1 and t.merchantNo = m.merchantNo and t.tranDate = ?3 "
				+ "and ( COALESCE(LENGTH(TRIM( ?2 )),0) = 0 or t.merchantNo = ?2 ) "
				+ "and COALESCE(LENGTH(TRIM(t.cancelTranSeqNo)),0) = 0 "
				+ "group by t.merchantNo";
		String ssql = "select t.merchantNo, sum(t.MRCHOTHERPAIDAMOUNT) from MCHADDTRADSUMMARY t, Merchant m "
				+ "where m.issueCompanyNo = ?1 and t.merchantNo = m.merchantNo and t.tranDate = ?3 "
				+ "and ( COALESCE(LENGTH(TRIM( ?2 )),0) = 0 or t.merchantNo = ?2 ) "
				+ "group by t.merchantNo";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo);
		if (PCPSUtil.isEmpty(merchantNo))
			query.setParameter(2, null);
		else
			query.setParameter(2, merchantNo);
		query.setParameter(3, tranDate);
		List<Object[]> obj_list = query.getResultList();
		if (obj_list == null)
			return null;

		Query squery = _em.createNativeQuery(ssql);
		squery.setParameter(1, issueCompanyNo);
		if (PCPSUtil.isEmpty(merchantNo))
			squery.setParameter(2, null);
		else
			squery.setParameter(2, merchantNo);
		squery.setParameter(3, tranDate);
		List<Object[]> sobj_list = squery.getResultList();
		if (sobj_list == null)
			return null;

		Map<String, BigInteger> moth = new HashMap<String, BigInteger>();
		for (Object[] oblist : sobj_list)
			moth.put(oblist[0].toString(), getValue(oblist[1]));

		// 类型转换
		List<MerchantDaySettlement> mds_list = new ArrayList<MerchantDaySettlement>();
		for (Object[] objarr : obj_list) {
			MerchantDaySettlement mds = new MerchantDaySettlement();
			mds.setIssueCompanyNo(issueCompanyNo);
			mds.setMerchantNo(objarr[0].toString());
			mds.setTranDate(tranDate);
			mds.setConsume(getValue(objarr[1]));
			mds.setConsumeCount(getValue(objarr[2]));
			mds.setDeposit(getValue(objarr[3]));
			mds.setDepositCount(getValue(objarr[4]));
			mds.setCancelPaid(getValue(objarr[5]));
			mds.setCancelPaidCount(getValue(objarr[6]));
			mds.setFee(getValue(objarr[7]));
			BigInteger otherpaid = moth.get(mds.getMerchantNo());
			mds.setOtherPaid(otherpaid == null ? BigInteger.ZERO : otherpaid);
			mds_list.add(mds);
		}

		return mds_list;
	}

	/**
	 * 根据发卡商编号及交易日期检索有商户列表
	 * 
	 * @param issueCompanyNo
	 * @param tranDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<String> getAllMerchantListByIssueCompanyNoAndTranDate(
			String issueCompanyNo, Date tranDate) {
		String sql = "select t.merchantNo "
				+ "from MchAddTradDetail t, Merchant m "
				+ "where m.issueCompany = ?1 and t.merchantNo = m.merchantNo and t.tranDate = ?2 "
				+ "group by t.merchantNo order by t.merchantNo";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, tranDate);
		List<String> mds_list = new ArrayList<String>();
		List<Object[]> obj_list = query.getResultList();
		if (obj_list == null)
			return mds_list;

		// 类型转换
		for (Object[] objarr : obj_list)
			mds_list.add(objarr[0].toString());

		return mds_list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MchTradDetail> getMchTradDetailListByCondition(
			String issueCompanyNo, String merchantNo, String mrchBrchNo,
			String terminalNo, String startTime, String endTime,
			BigInteger startAmount, BigInteger endAmount, String tranCode,
			String cardNo, Integer page) {
		if (PCPSUtil.isEmpty(issueCompanyNo)) {
			if (!PCPSUtil.isEmpty(merchantNo)) {
				issueCompanyNo = PCPSUtil.getICNoByBranchNo(merchantNo);
			} else {
				return null;
			}
		}
		String sql = "select * from ((select "
				+ allcolumns
				+ " from MchTradDetail where merchantNo like '"
				+ issueCompanyNo
				+ "%' and (?1 is null or merchantNo = ?1) and (?2 is null or mrchBrchNo=?2) and (?3 is null or terminalNo=?3) "
				+ "and tranDate>= to_date(?4,'yyyy-mm-dd hh24:mi:ss') and tranDate<=to_date(?5,'yyyy-mm-dd hh24:mi:ss') and (?6 is null or Amount >=to_number(?6)) and (?7 is null or Amount<=to_number(?7)) and (?8 is null or tranCode=?8) and (?9 is null or CardNo=?9) )union all"
				+ "(select "
				+ allcolumns
				+ " from MchHisTradDetail where merchantNo like '"
				+ issueCompanyNo
				+ "%' and (?1 is null or merchantNo = ?1) and (?2 is null or mrchBrchNo=?2) and (?3 is null or terminalNo=?3) "
				+ "and tranDate>= to_date(?4,'yyyy-mm-dd hh24:mi:ss') and tranDate<=to_date(?5,'yyyy-mm-dd hh24:mi:ss') and (?6 is null or Amount >=to_number(?6)) and (?7 is null or Amount<=to_number(?7)) and (?8 is null or tranCode=?8) and (?9 is null or CardNo=?9) )) order by MerchantNo,TranDate,TranSeqNo";
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		Query query = _em.createNativeQuery(sql, MchTradDetail.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		query.setParameter(1, merchantNo);
		query.setParameter(2, mrchBrchNo);
		query.setParameter(3, terminalNo);
		query.setParameter(4, startTime + " 00:00:00");
		query.setParameter(5, endTime + " 23:59:59");
		query.setParameter(6, startAmount);
		query.setParameter(7, endAmount);
		query.setParameter(8, tranCode);
		query.setParameter(9, cardNo);
		return query.getResultList();
	}

	@Override
	public Integer getMchTradDetailTotalByCondition(String issueCompanyNo,
			String merchantNo, String mrchBrchNo, String terminalNo,
			String startTime, String endTime, BigInteger startAmount,
			BigInteger endAmount, String tranCode, String cardNo) {
		if (PCPSUtil.isEmpty(issueCompanyNo)) {
			if (!PCPSUtil.isEmpty(merchantNo)) {
				issueCompanyNo = PCPSUtil.getICNoByBranchNo(merchantNo);
			} else {
				return 0;
			}
		}
		String sql = "select nvl(count(*),0) from ((select "
				+ allcolumns
				+ " from MchTradDetail where merchantNo like '"
				+ issueCompanyNo
				+ "%' and (?1 is null or merchantNo = ?1) and (?2 is null or mrchBrchNo=?2) and (?3 is null or terminalNo=?3) "
				+ "and tranDate>= to_date(?4,'yyyy-mm-dd hh24:mi:ss') and tranDate<=to_date(?5,'yyyy-mm-dd hh24:mi:ss') and (?6 is null or Amount >=to_number(?6)) and (?7 is null or Amount<=to_number(?7)) and (?8 is null or tranCode=?8) and (?9 is null or CardNo=?9) )union all"
				+ "(select "
				+ allcolumns
				+ " from MchHisTradDetail where merchantNo like '"
				+ issueCompanyNo
				+ "%' and (?1 is null or merchantNo = ?1) and (?2 is null or mrchBrchNo=?2) and (?3 is null or terminalNo=?3) "
				+ "and tranDate>= to_date(?4,'yyyy-mm-dd hh24:mi:ss') and tranDate<=to_date(?5,'yyyy-mm-dd hh24:mi:ss') and (?6 is null or Amount >=to_number(?6)) and (?7 is null or Amount<=to_number(?7)) and (?8 is null or tranCode=?8) and (?9 is null or CardNo=?9) )) order by trantime desc";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantNo);
		query.setParameter(2, mrchBrchNo);
		query.setParameter(3, terminalNo);
		query.setParameter(4, startTime + " 00:00:00");
		query.setParameter(5, endTime + " 23:59:59");
		query.setParameter(6, startAmount);
		query.setParameter(7, endAmount);
		query.setParameter(8, tranCode);
		query.setParameter(9, cardNo);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getDepositReportListByCondition(
			PCPSWebInput webInput, String merchantNo, String merchantBrchNo,
			String startTime, String endTime) {
		if (webInput == null || webInput.getOperId() == null
				|| PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String sqlcond = "";
		if (!PCPSUtil.isEmpty(merchantBrchNo))
			sqlcond = "and m.mrchbrchno='" + merchantBrchNo + "'";
		String sql = "select m.terminalno,m.merchantno,t.merchantname,m.mrchbrchno,mb.merchantbranchname,to_char(m.trandate,'yyyy-mm-dd') trantdate,a.InitialBalance,'1',m.cardno,m.amount,"
				+ "case when m.trancode='9040' then '卡充值' when m.trancode='9050' then '卡充值撤销' end as trancode from MchTradDetail m,activatecard a,merchant t,MerchantBranch mb "
				+ "where m.cardno=a.cardno and m.merchantno=t.merchantno and m.mrchbrchno=mb.merchantbranchno(+) and (m.TranCode='9040' or m.trancode='9050') and "
				+ "(m.canceltranseqno is null or length(trim(m.canceltranseqno))=0) and (?1 is null or m.merchantno=?1) "
				+ sqlcond
				+ " and substr(m.merchantno,0,6) = ?2"
				+ " and m.trandate between to_date(?3,'yyyy-mm-dd') and to_date(?4,'yyyy-mm-dd') order by m.trandate";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantNo);
		query.setParameter(2, PCPSUtil.getIssueCompanyNo(webInput));
		query.setParameter(3, startTime);
		query.setParameter(4, endTime);
		List<Object[]> list = query.getResultList();
		// 类型转换
		// DepositReport dr = null;
		// List<DepositReport> resultlist = new ArrayList<DepositReport>();
		// for (Object[] objArr : list) {
		// dr = new DepositReport();
		// dr.setPosNum(objArr[1].toString());
		// dr.setMerchantNo(objArr[2].toString());
		// dr.setMerchantName(objArr[3].toString());
		// dr.setMerchantBrchNo(objArr[4] == null ? "" : objArr[4].toString());
		// dr.setMerchantBrchName(objArr[5] == null ? "" : objArr[5]
		// .toString());
		// dr.setTime(objArr[6].toString());
		// dr.setMianzhi(Integer.parseInt(objArr[7].toString()));
		// dr.setBishu(Integer.parseInt(objArr[8].toString()));
		// dr.setCardno(objArr[9].toString());
		// dr.setAmount(new BigDecimal(objArr[10].toString()));
		// dr.setRemark(objArr[11].toString());
		// resultlist.add(dr);
		// }
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getDepositReportCountByCondition(
			PCPSWebInput webInput, String merchantNo, String merchantBrchNo,
			String startTime, String endTime) {
		if (webInput == null || webInput.getOperId() == null
				|| PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String sqlcond1 = "";
		String sqlcond2 = "";
		if (!PCPSUtil.isEmpty(merchantNo))
			sqlcond1 = "and m.merchantNo like '" + merchantNo + "%'";
		if (!PCPSUtil.isEmpty(merchantBrchNo))
			sqlcond2 = "and m.mrchbrchno like '" + merchantBrchNo + "%'";
		String sql = "select mt.merchantno,t.merchantname,mt.mrchbrchno,mb.merchantbranchname,mt.count,mt.amount,mt.trancode from "
				+ "(select m.merchantno,m.mrchbrchno,count(*) count,sum(m.amount) amount,case when m.trancode='9040' then '卡充值' when m.trancode='9050' then '卡充值撤销' end as trancode "
				+ "from MchTradDetail m where (m.TranCode='9040' or m.trancode='9050') and (m.canceltranseqno is null or length(trim(m.canceltranseqno))=0) "
				+ sqlcond1
				+ " "
				+ sqlcond2
				+ " and substr(m.merchantno,0,6) = ?1"
				+ " and m.trandate between to_date(?2,'yyyy-mm-dd') and to_date(?3,'yyyy-mm-dd') "
				+ "group by m.merchantno,m.mrchbrchno,m.trancode) mt,merchant t,MerchantBranch mb where mt.merchantno=t.merchantno and mt.mrchbrchno=mb.merchantbranchno(+) order by mt.merchantno,mt.mrchbrchno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, PCPSUtil.getIssueCompanyNo(webInput));
		query.setParameter(2, startTime);
		query.setParameter(3, endTime);
		List<Object[]> list = query.getResultList();
		// 类型转换
		// DepositReport dr = null;
		// List<DepositReport> resultlist = new ArrayList<DepositReport>();
		// for (Object[] objArr : list) {
		// dr = new DepositReport();
		// dr.setMerchantNo(objArr[0].toString());
		// dr.setMerchantName(objArr[1].toString());
		// dr.setMerchantBrchNo(objArr[2] == null ? "" : objArr[2].toString());
		// dr.setMerchantBrchName(objArr[3] == null ? "" : objArr[3]
		// .toString());
		// dr.setMianzhi(objArr[4].toString());
		// dr.setAmount(objArr[5].toString());
		// dr.setRemark(objArr[6].toString());
		// resultlist.add(dr);
		// }
		return list;
	}

	/**
	 * 卡-7:卡消费日报明细表
	 * 
	 * @param webInput
	 * @param saleBranchNo
	 * @param isSingle
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getCardConsumeDetailReportByCondition(
			PCPSWebInput webInput, String branchNo, String starttranDate,
			String endtranDate) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(starttranDate)
				|| PCPSUtil.isEmpty(endtranDate))
			return null;
		String sqlcond = PCPSUtil.getSqlCond(webInput, branchNo,
				"a.openbranchno");
		final String sql = "select t.*, t1.mrchseqno, t1.trantime, t1.trancode, to_char(t1.trandate,'yyyy-mm-dd'),to_char(t1.OtherTranDate,'yyyy-mm-dd') "
				+ " from (select a.TranSeqNo, b.batchno, b.cardseqno, b.cardno, c.salebranchname,a.paidamount, "
				+ " a.feeamount,m.merchantname,m.merchantno,a.trandate, "
				+ " (select nvl(s.MasBalance,0) "
				+ " from AccountSystemAddTradDetail s "
				+ " where s.AccSeqNo = a.accseqno "
				+ " and s.MasterAccNo = a.masteraccno "
				+ " and s.SubAccNo = a.subaccno "
				+ " and s.accsubseqno = a.accsubseqno"
				+ " and a.paidamount + a.feeamount = s.debitamount "
				+ " and s.debitamount <> 0) as masbalance, "
				+ " a.accsubseqno "
				+ " from MchAccDetail a, "
				+ " ActivateCard  b, "
				+ " Salebranch    c, "
				+ " Cardclassic   d, "
				+ " Merchant      m, "
				+ " MakeCardControl s "
				+ " where a.cardno = b.cardno "
				+ " and a.paidamount + a.feeamount <> 0 "
				+ " and a.openbranchno = c.salebranchno "
				+ sqlcond
				+ " and b.cardclassicid = d.cardclassicid "
				+ " and s.batchno = b.batchno "
				+ " and a.merchantno = m.merchantno "
				+ " and a.trandate >= to_date(?1, 'yyyy-MM-dd')"
				+ " and a.trandate <= to_date(?2, 'yyyy-MM-dd')	) t, "
				+ " (select merchantno, mrchseqno, trandate, transeqno, trantime, OtherTranDate, trancode, cancelTranSeqNo "
				+ " from MchTradDetail "
				+ " where trandate >= to_date(?1, 'yyyy-MM-dd') "
				+ " and trandate <= to_date(?2, 'yyyy-MM-dd') "
				+ " and trancode in ('9010', '9020') "
				+ " and COALESCE(LENGTH(TRIM(cancelTranSeqNo)), 0) = 0 "
				+ " union "
				+ " select merchantno, mrchseqno,  trandate,  transeqno, trantime, OtherTranDate, trancode, cancelTranSeqNo "
				+ " from MchHisTradDetail "
				+ " where trandate >= to_date(?1, 'yyyy-MM-dd') "
				+ " and trandate <= to_date(?2, 'yyyy-MM-dd')"
				+ " and trancode in ('9010', '9020') "
				+ " and COALESCE(LENGTH(TRIM(cancelTranSeqNo)), 0) = 0) t1 "
				+ " where t.merchantno = t1.merchantno "
				+ " and t.trandate = t1.trandate "
				+ " and t.TranSeqNo = t1.transeqno "
				+ " and t1.trandate >= to_date(?1, 'yyyy-MM-dd') "
				+ " and t1.trandate <= to_date(?2, 'yyyy-MM-dd') "
				+ " and t1.trancode in ('9010', '9020') "
				+ " and COALESCE(LENGTH(TRIM(t1.cancelTranSeqNo)), 0) = 0 "
				+ " order by t1.transeqno, t1.trantime, t.cardno, t.accsubseqno ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, starttranDate);
		query.setParameter(2, endtranDate);
		List<Object[]> list = query.getResultList();
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getMchPOSTradDetailDaySettReport(
			PCPSWebInput webInput, String date) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(date))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);

		String sql = "select a.transeqno,a.cardno,c.salebranchname,a.factscorepaidamount,a.amount,a.tranDate,d.merchantbranchname,a.terminalno,a.trancode,a.trantime,a.tdate,a.otdate from"
				+ " (select transeqno,tranDate,mrchbrchNo,MerchantNo,CardNo,factscorepaidamount,amount,trantime,terminalno,trancode,to_char(trandate,'yyyy-mm-dd') as tdate,to_char(OtherTranDate,'yyyy-mm-dd') as otdate from MchTradDetail where trandate=to_date(?2,'yyyy-MM-dd') and trancode <> ?3"
				+ " union all select transeqno,tranDate,mrchbrchNo,MerchantNo,CardNo,factscorepaidamount,amount,trantime,terminalno,trancode,to_char(trandate,'yyyy-mm-dd') as tdate,to_char(OtherTranDate,'yyyy-mm-dd') as otdate from MchHisTradDetail where trandate=to_date(?2,'yyyy-MM-dd') and trancode <> ?3"
				+ " ) a,activatecard b,salebranch c,merchantbranch d,merchant e where a.mrchbrchNo=d.merchantbranchno and a.cardno=b.cardno and d.merchantno=e.merchantno"
				+ " and b.salebranchno=c.salebranchno and e.merchantno=a.merchantno and e.ChannelMode='01' and e.issuecompanyno=?1 ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, date);
		query.setParameter(3, MchTradDetail.TRANCODE_DEPOSIT);
		List<Object[]> list = query.getResultList();
		return list;
	}

	/**
	 * 根据时间段检索商户交易流水及商户历史交易流水，预警情况
	 * 
	 * @param sratTime
	 *            起始时间
	 * @param endTime
	 *            终止时间 return list<Object[]>
	 */
	public List<Object[]> getProxybuyerSaleOrderNum(String sratTime,
			String endTime) {
		if (PCPSUtil.isEmpty(sratTime) || PCPSUtil.isEmpty(endTime))
			return null;
		// String sql="";
		// Query query = _em.createNativeQuery(sql);
		// List<Object[]> list = query.getResultList();
		// return list;
		return null;
	}

	/**
	 * 根据商户号统计该日累计消费金额及笔数
	 * 
	 * @param merchantNo
	 * @param tranDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public MchTradDetail getMchTradDetailDayPaidSummary(String merchantNo,
			Date tranDate) {
		String sql = "select "
				// 消费
				+ "nvl(sum(case when t.tranCode = '9010' then t.Amount else 0 end),0), "
				// 消费笔数
				+ "nvl(sum(case when t.tranCode = '9010' then 1 else 0 end),0) "
				+ "from MchTradDetail t "
				+ "where t.merchantNo = ?1 and t.tranDate = ?2 "
				+ "and COALESCE(LENGTH(TRIM(t.cancelTranSeqNo)),0) = 0 ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantNo);
		query.setParameter(2, tranDate);

		MchTradDetail mtd = new MchTradDetail();
		mtd.setAmount(BigInteger.ZERO);
		mtd.setFee(BigInteger.ZERO);

		List<Object[]> ml = query.getResultList();
		if (ml == null || ml.size() != 1)
			return mtd;

		Object[] ob = ml.get(0);
		if (ob == null || ob.length != 2)
			return mtd;

		mtd.setAmount(new BigInteger(ob[0].toString()));
		mtd.setFee(new BigInteger(ob[1].toString()));

		return mtd;
	}

	@SuppressWarnings("unchecked")
	private BigInteger[] getMchTradDetailDayDepositSummary(String tabname,
			String merchantNo, Date startDate, Date endDate) {
		String sql = "select "
				// 充值
				+ "nvl(sum(case when t.tranCode = '9040' then t.Amount else 0 end),0), "
				// 充值笔数
				+ "nvl(sum(case when t.tranCode = '9040' then 1 else 0 end),0) "
				+ "from "
				+ tabname
				+ " t where t.merchantNo = ?1 and t.tranDate between ?2 and ?3 "
				+ "and COALESCE(LENGTH(TRIM(t.cancelTranSeqNo)),0) = 0 ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantNo);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);

		BigInteger[] val = new BigInteger[2];
		val[0] = val[1] = BigInteger.ZERO;

		List<Object[]> ml = query.getResultList();
		if (ml == null || ml.size() != 1)
			return val;

		Object[] ob = ml.get(0);
		if (ob == null || ob.length != 2)
			return val;

		val[0] = new BigInteger(ob[0].toString());
		val[1] = new BigInteger(ob[1].toString());

		return val;
	}

	/**
	 * 根据商户号统计该日累计充值金额及笔数
	 * 
	 * @param merchantNo
	 * @param tranDate
	 * @return
	 */
	public MchTradDetail getMchTradDetailDayDepositSummary(String merchantNo,
			Date tranDate, Integer cycleLimit) {

		MchTradDetail mtd = new MchTradDetail();
		mtd.setAmount(BigInteger.ZERO);
		mtd.setFee(BigInteger.ZERO);

		java.util.Calendar cal = java.util.Calendar.getInstance();
		cal.setTime(PCPSUtil.getZeroedHMS(tranDate));
		cal.add(java.util.Calendar.DAY_OF_MONTH, 1 - cycleLimit);
		Date startDate = cal.getTime();

		BigInteger[] val_cur = getMchTradDetailDayDepositSummary(
				"MchTradDetail", merchantNo, startDate, tranDate);

		BigInteger[] val_his = getMchTradDetailDayDepositSummary(
				"MchHisTradDetail", merchantNo, startDate, tranDate);

		mtd.setAmount(val_cur[0].add(val_his[0]));
		mtd.setFee(val_cur[1].add(val_his[1]));

		return mtd;
	}

	/**
	 * 统计指定卡指定日期在商户处的充值合计
	 * 
	 * @param cardNo
	 * @param tranDate
	 * @return
	 */
	public BigInteger getDayDepositByCardNo(String cardNo, Date tranDate) {
		String sql = "select nvl(sum(t.Amount),0) "
				+ "from MchTradDetail t "
				+ "where t.cardNo = ?1 and t.tranDate = ?2 and t.tranCode = '9040' "
				+ "and COALESCE(LENGTH(TRIM(t.cancelTranSeqNo)),0) = 0 ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, cardNo);
		query.setParameter(2, tranDate);

		Object obj = query.getSingleResult();
		if (obj == null)
			return BigInteger.ZERO;
		return new BigInteger(obj.toString());
	}

	/**
	 * 汇总某商户某日联机交易(不包含调帐)
	 * 
	 * @param merchantNo
	 * @param tranDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private List<Object[]> checkAccountSummarySql(String fromtab,
			String merchantNo, Date tranDate) {
		final String sumsql = "select "
				// 消费
				+ "nvl(sum(case when t.tranCode = '9010' then t.Amount + t.ScorePaidAmount else 0 end),0), "
				// 消费笔数
				+ "nvl(sum(case when t.tranCode = '9010' then 1 else 0 end),0), "
				// 充值
				+ "nvl(sum(case when t.tranCode = '9040' or t.tranCode = '9050' then t.Amount else 0 end),0), "
				// 充值笔数
				+ "nvl(sum(case when t.tranCode = '9040' or t.tranCode = '9050' then 1 else 0 end),0), "
				// 退货
				+ "nvl(sum(case when t.tranCode = '9020' or t.tranCode = '9030' then abs(t.Amount) else 0 end),0), "
				// 退货笔数
				+ "nvl(sum(case when t.tranCode = '9020' or t.tranCode = '9030' then 1 else 0 end),0) "
				+ "from " + fromtab
				+ " t where t.merchantNo = ?1 and t.tranDate = ?2 "
				+ "and COALESCE(LENGTH(TRIM(t.cancelTranSeqNo)),0) = 0 "
				+ "and (t.MRCHSEQNO not like '%ADJUST') ";
		Query query = _em.createNativeQuery(sumsql);
		query.setParameter(1, merchantNo);
		query.setParameter(2, tranDate);
		return query.getResultList();
	}

	private BigInteger getValue(Object[] obj, int idx) {
		if (obj == null || idx >= obj.length || idx < 0)
			return BigInteger.ZERO;

		return new BigInteger(obj[idx].toString());
	}

	/**
	 * 汇总某商户某日联机交易(不包含调帐)
	 * 
	 * @param merchantNo
	 * @param tranDate
	 * @return
	 */
	public BigInteger[] checkAccountSummary(String merchantNo, Date tranDate) {
		// 下标: 0 - 借方笔数 1 - 借方金额 2 - 贷方笔数 3 - 贷方金额
		BigInteger[] all = new BigInteger[4];
		all[0] = all[1] = all[2] = all[3] = BigInteger.ZERO;
		List<Object[]> cur = checkAccountSummarySql("MchTradDetail",
				merchantNo, tranDate);
		List<Object[]> his = checkAccountSummarySql("MchHisTradDetail",
				merchantNo, tranDate);

		if (cur != null && cur.size() == 1) {
			Object[] obj = cur.get(0);
			// 借方笔数
			all[0] = all[0].add(getValue(obj, 1));
			all[0] = all[0].add(getValue(obj, 5));
			// 借方金额
			all[1] = all[1].add(getValue(obj, 0));
			all[1] = all[1].subtract(getValue(obj, 4));
			// 贷方笔数
			all[2] = all[2].add(getValue(obj, 3));
			// 贷方金额
			all[3] = all[3].add(getValue(obj, 2));
		}

		if (his != null && his.size() == 1) {
			Object[] obj = his.get(0);
			// 借方笔数
			all[0] = all[0].add(getValue(obj, 1));
			all[0] = all[0].add(getValue(obj, 5));
			// 借方金额
			all[1] = all[1].add(getValue(obj, 0));
			all[1] = all[1].subtract(getValue(obj, 4));
			// 贷方笔数
			all[2] = all[2].add(getValue(obj, 3));
			// 贷方金额
			all[3] = all[3].add(getValue(obj, 2));
		}

		return all;
	}

	/**
	 * 根据交易日期获取实名持卡人交易信息 预警规则:同一实名制个人客户，不同卡号N天内刷卡消费累计超过N元
	 * 
	 * @param issueCompanyNo
	 * @param amount
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getRealNameCardFromMchTradDetail(
			String issueCompanyNo, String startDate, String endDate,
			String cards) {
		String sql = "select p.name,t.merchantno,m.merchantname,t.transeqno,a.masteraccno,t.amount,p.personalid"
				+ " from mchtraddetail t,activatecard a,personalcustom p,merchant m"
				+ " where substr(t.merchantno,0,6)=?1"
				+ " and t.trandate = to_date(?2,'yyyy-mm-dd')"
				+ " and t.trandate <= to_date(?3,'yyyy-mm-dd')"
				+ " and t.cardno = a.cardno"
				+ " and a.ownerid = p.personalid"
				+ " and t.merchantno = m.merchantno"
				+ " and p.personalid in(select acc.ownerid from activatecard acc where acc.ownerid is not null group by acc.ownerid having count(*)>1)"
				+ " and t.cardno in(" + cards + ") order by p.name";

		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		query.setParameter(4, cards);
		return query.getResultList();
	}

	/**
	 * 判断是否符合累计金额限制 预警规则:同一实名制个人客户，不同卡号N天内刷卡消费累计超过N元
	 * 
	 * @param issueCompanyNo
	 * @param startDate
	 * @param endDate
	 * @param cards
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public boolean isEnoughAmountCardsFromMchTradDetail(String issueCompanyNo,
			String startDate, String endDate, String cards, BigInteger amount) {
		String sql = "select sum(t.amount) from mchtraddetail t"
				+ " where substr(t.merchantno,0,6)=?1"
				+ " and t.trandate >= to_date(?2,'yyyy-mm-dd')"
				+ " and t.trandate <= to_date(?3,'yyyy-mm-dd')"
				+ " and t.cardno in(" + cards + ") "
				+ " having sum(t.amount)>?4";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		query.setParameter(4, amount);
		List<Object[]> list = query.getResultList();
		if (list != null && list.size() > 0) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * 根据交易时间，发卡商编号 按卡号，数量，最大时间分组检索
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getMchTradDetailByTime(String issuecompanno,
			Date trandate, int number) {
		if (PCPSUtil.isEmpty(issuecompanno) || trandate == null)
			return null;
		String sql = "select distinct (CardNo) CardNo,nvl(count(CardNo), 0) count"
				+ " from MchTradDetail"
				+ " where Merchantno in(select Merchantno from Merchant where IssueCompanyNo = ?1)"
				+ " and TranDate=?2"
				+ " group by CardNo having nvl(count(CardNo), 0) >= ?3";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanno);
		query.setParameter(2, trandate);
		query.setParameter(3, number);
		return query.getResultList();
	}

	/**
	 * 根据卡号,账务日期获取交易明细
	 * 
	 * @param cardno
	 * @param tranDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<MchTradDetail> getMchTradDetailByCardNoAndTranDate(
			String cardno, Date tranDate) {
		String sql = "select * from MchTradDetail a"
				+ " where a.cardno = ?1 and a.trandate= ?2 order by a.trantime";
		Query query = _em.createNativeQuery(sql, MchTradDetail.class);
		query.setParameter(1, cardno);
		query.setParameter(2, tranDate);
		return query.getResultList();
	}

	@Override
	public Integer getMchTradDetailByCardNoAndTranDate(String cardno,
			Date startDate, Date endDate, Date tranDate) {
		String sql = "select nvl(count(*),0) from MchTradDetail a "
				+ " where a.cardno = ?1 and a.trantime>= ?2"
				+ " and a.trantime<= ?3 and a.trandate= ?4";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, cardno);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		query.setParameter(4, tranDate);
		return Integer.valueOf(query.getSingleResult().toString());
	}

	/**
	 * 根据交易时间，卡号 查询
	 */
	@SuppressWarnings("unchecked")
	public List<MchTradDetail> getMchTradDetailByCardNo(Date startdate,
			Date enddate, String cardno, Date tranDate) {
		if (startdate == null || enddate == null || PCPSUtil.isEmpty(cardno))
			return null;

		String sql = " select * from MchTradDetail where "
				+ " CardNo=?1 and TranTime>=?2 and TranTime<= ?3 and trandate = ?4";
		Query query = _em.createNativeQuery(sql, MchTradDetail.class);
		query.setParameter(1, cardno);
		query.setParameter(2, startdate);
		query.setParameter(3, enddate);
		query.setParameter(4, tranDate);
		return query.getResultList();
	}

	/**
	 * 获取N天内渠道交易笔数（N为预警规则参数） 预警规则：对于N天内无交易的渠道，系统即进行风险预警
	 * 
	 * @param tranDate
	 * @param days
	 * @param issueCompanyNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getMchDealSum(Date tranDate, int days,
			String issueCompanyNo) {
		String sql = "select a.merchantno, sum(decode(d.transeqno, null, 0, 1)) dealcount "
				+ "from merchant a "
				+ "left join (select b.merchantno merchantno, b.transeqno transeqno, b.trandate trandate from mchtraddetail b "
				+ "where b.trandate <= ?2 and b.trandate >= (?3 - ?4) "
				+ "union all select c.merchantno merchantno, c.transeqno transeqno, c.trandate trandate from mchhistraddetail c "
				+ "where c.trandate <= ?2 and c.trandate >= (?3 - ?4)) d "
				+ "on a.merchantno = d.merchantno "
				+ "where a.issuecompanyno = ?1 " + "group by a.merchantno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, tranDate);
		query.setParameter(3, tranDate);
		query.setParameter(4, days);
		return query.getResultList();
	}

	/**
	 * 获取卡在一天内小额交易的次数 预警规则：同一张卡，同一天小额（小于100元）多次（五次以上）的刷卡
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getCardDealSum(Date tranDate, BigInteger amount) {
		String sql = "select a.cardno, count(a.mrchseqno) mrchseqno from "
				+ "(select b.cardno cardno, b.mrchseqno mrchseqno from mchtraddetail b  "
				+ "where b.trandate = ?1 "
				+ "and b.AMOUNT < ?2 "
				+ "union all select c.cardno cardno, c.mrchseqno mrchseqno from MchHisTradDetail c "
				+ "where c.trandate = ?1 " + "and c.AMOUNT < ?2) a "
				+ "group by a.cardno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, tranDate);
		query.setParameter(2, amount);
		return query.getResultList();
	}

	/**
	 * 根据卡号获取卡最后一次交易的信息
	 * 
	 * @param cardNo
	 * @param amount
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getMchTradDetailByCardLastDeal(String issueCompany,
			String cardNo, BigInteger amount, Date tranDate) {
		String sql = "select * from mchtraddetail a "
				+ "where substr(a.merchantno,0,6) = ?1 and a.trandate = ?2 and a.cardno = ?3 and a.amount < ?4 order by a.trantime";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompany);
		query.setParameter(2, tranDate);
		query.setParameter(3, cardNo);
		query.setParameter(4, amount);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getCardsAmountLimitFromMrchTradDetail(
			String issueCompanyNo, String startDate, String endDate,
			BigInteger amount) {
		String sql = "select t.cardno,sum(t.amount) from mchtraddetail t"
				+ " where substr(t.merchantno,0,6)=?1"
				+ " and t.trandate >= to_date(?2,'yyyy-mm-dd')"
				+ " and t.trandate <= to_date(?3,'yyyy-mm-dd')"
				+ " group by t.cardno having sum(t.amount)>?4";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		query.setParameter(4, amount);

		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getMrchTradDetailByCardNo(String issueCompanyNo,
			String startDate, String endDate, String cardNo) {
		String sql = "select t.merchantno,m.merchantname,t.transeqno,p.name,p.personalid"
				+ " from mchtraddetail t,merchant m,personalcustom p,activatecard a"
				+ " where substr(t.merchantno,0,6)=?1"
				+ " and t.trandate >= to_date(?2,'yyyy-mm-dd')"
				+ " and t.trandate <= to_date(?3,'yyyy-mm-dd')"
				+ " and t.cardno = ?4 and t.merchantno = m.merchantno"
				+ " and p.personalid=a.ownerid and a.cardno = t.cardno"
				+ " order by t.trantime desc";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		query.setParameter(4, cardNo);

		return query.getResultList();
	}

	// 预警规则13 满足条件的终端号
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getAlarmRule13OverTermimalNoList(String icNo,
			BigInteger amount, String startDate, String endDate) {
		String sql = "select TerminalNo from (select t.TerminalNo,sum(t.amount) amount from mchtraddetail t where t.trancode='9010' and t.CancelTranSeqNo is null and t.merchantno like '"
				+ icNo
				+ "%' "
				+ "and t.TerminalNo is not null and t.trandate between to_date('"
				+ startDate
				+ "','yyyy-mm-dd') and to_date('"
				+ endDate
				+ "','yyyy-mm-dd') "
				+ "group by t.TerminalNo having sum(t.amount)>="
				+ amount
				+ " union select t.TerminalNo,sum(t.amount) amount from mchhistraddetail t where t.trancode='9010' and t.CancelTranSeqNo is null and t.merchantno like '"
				+ icNo
				+ "%' "
				+ "and t.TerminalNo is not null and t.trandate between to_date('"
				+ startDate
				+ "','yyyy-mm-dd') and to_date('"
				+ endDate
				+ "','yyyy-mm-dd') "
				+ "group by t.TerminalNo having sum(t.amount)>=" + amount + ")";
		Query query = _em.createNativeQuery(sql);
		return query.getResultList();
	}

	// N天POS消费超过N元的交易明细列表
	@SuppressWarnings("unchecked")
	@Override
	public List<MchTradDetail> getAlarmRule13MchTradDetailListByCond(
			String icNo, String terminalNo, String startDate, String endDate) {
		String sql = "select * from(select "
				+ allcolumns
				+ " from mchtraddetail t where t.trancode='9010' and t.CancelTranSeqNo is null and t.merchantno like '"
				+ icNo
				+ "%' "
				+ "and t.TerminalNo is not null and t.terminalno ='"
				+ terminalNo
				+ "' and t.trandate between to_date('"
				+ startDate
				+ "','yyyy-mm-dd') and to_date('"
				+ endDate
				+ "','yyyy-mm-dd') "
				+ "union select "
				+ allcolumns
				+ " from mchhistraddetail t where t.trancode='9010' and t.CancelTranSeqNo is null and t.merchantno like '"
				+ icNo + "%' "
				+ "and t.TerminalNo is not null and t.terminalno ='"
				+ terminalNo + "' and t.trandate between to_date('" + startDate
				+ "','yyyy-mm-dd') and to_date('" + endDate
				+ "','yyyy-mm-dd'))";
		Query query = _em.createNativeQuery(sql, MchTradDetail.class);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public String getMerchantNoByTerminalNo(String icNo, String startDate,
			String endDate, String terminalNo) {
		String sql = "select * from (select distinct(merchantno) from mchtraddetail t where t.trancode='9010' and t.CancelTranSeqNo is null and t.merchantno like '"
				+ icNo
				+ "%' "
				+ "and t.TerminalNo is not null and t.trandate between to_date('"
				+ startDate
				+ "','yyyy-mm-dd') and to_date('"
				+ endDate
				+ "','yyyy-mm-dd') union select distinct(merchantno) from mchhistraddetail t where t.trancode='9010' and t.CancelTranSeqNo is null and t.merchantno like '"
				+ icNo
				+ "%' "
				+ "and t.TerminalNo is not null and t.trandate between to_date('"
				+ startDate
				+ "','yyyy-mm-dd') and to_date('"
				+ endDate
				+ "','yyyy-mm-dd')) ";
		Query query = _em.createNativeQuery(sql);
		List<Object> obj = query.getResultList();
		if (obj == null || obj.size() < 1) {
			return "";
		}
		return obj.get(0).toString();
	}

	// 预警规则19 满足条件的商户号
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getAlarmRule19OverMerchantNoList(String icNo,
			BigInteger amount, String startDate, String endDate) {
		String sql = "select merchantno from (select t.merchantno,sum(t.amount) amount from mchtraddetail t where t.trancode='9010' and t.CancelTranSeqNo is null and t.merchantno like '"
				+ icNo
				+ "%' "
				+ "and t.TerminalNo is not null and t.trandate between to_date('"
				+ startDate
				+ "','yyyy-mm-dd') and to_date('"
				+ endDate
				+ "','yyyy-mm-dd') "
				+ "group by t.merchantno having sum(t.amount)>="
				+ amount
				+ " union select t.merchantno,sum(t.amount) amount from mchhistraddetail t where t.trancode='9010' and t.CancelTranSeqNo is null and t.merchantno like '"
				+ icNo
				+ "%' "
				+ "and t.TerminalNo is not null and t.trandate between to_date('"
				+ startDate
				+ "','yyyy-mm-dd') and to_date('"
				+ endDate
				+ "','yyyy-mm-dd') "
				+ "group by t.merchantno having sum(t.amount)>=" + amount + ")";
		Query query = _em.createNativeQuery(sql);
		return query.getResultList();
	}

	// 预警规则19 同一商户号下的交易明细
	@SuppressWarnings("unchecked")
	@Override
	public List<MchTradDetail> getAlarmRule19MchTradDetailListByCond(
			String merchantNo, String startDate, String endDate) {
		String sql = "select * from(select "
				+ allcolumns
				+ " from mchtraddetail t where t.trancode='9010' and t.CancelTranSeqNo is null and t.merchantno ='"
				+ merchantNo
				+ "' and t.TerminalNo is not null and t.trandate between to_date('"
				+ startDate
				+ "','yyyy-mm-dd') and to_date('"
				+ endDate
				+ "','yyyy-mm-dd') "
				+ "union select "
				+ allcolumns
				+ " from mchhistraddetail t where t.trancode='9010' and t.CancelTranSeqNo is null and t.merchantno ='"
				+ merchantNo
				+ "' and t.TerminalNo is not null and t.trandate between to_date('"
				+ startDate + "','yyyy-mm-dd') and to_date('" + endDate
				+ "','yyyy-mm-dd'))";
		Query query = _em.createNativeQuery(sql, MchTradDetail.class);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getMchTradDetailCountReport(PCPSWebInput webInput,
			String tranDate, String merchantNoStr) {
		if (PCPSUtil.isEmpty(tranDate) || PCPSUtil.isEmpty(merchantNoStr))
			return null;
		String sql = "select a.TranSeqNo,(a.Amount+b.MrchOtherPaidAmount)/100,a.Amount/100, "
				+ " b.MrchOtherPaidAmount/100 from MchTradDetail a,MchAddTradSummary b "
				+ " where a.trandate = to_date(?1,'yyyy-mm-dd') and a.canceltranseqno is null "
				+ " and a.trancode in( '9010','9020') and a.MerchantNo in ("
				+ merchantNoStr
				+ ") "
				+ " and a.merchantno = b.merchantno and a.trandate = b.trandate "
				+ " and a.transeqno = b.transeqno ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, tranDate);
		List<Object[]> objList = query.getResultList();
		return objList;
	}
}